package utils;

import com.github.pjfanning.xlsx.StreamingReader;
import entity.item;
import entity.taskInfo;
import entity.worker;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import entity.ActualData;
import pojo.TaskAssignment;

public class ExcelReader {

    /**
     * 读取任务信息表（带空数据安全处理）
     */
    public static List<taskInfo> readTaskInfo(String filePath) {

        List<taskInfo> dataList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = StreamingReader.builder()
                     .rowCacheSize(10 * 10) //缓存到内存中的行数，默认是10
                     .bufferSize(1024 *4) //读取资源时，缓存到内存的字节大小，默认是1024
                     .open(fis)) { //打开资源，可以是InputStream或者是File，只能打开.xlsx格式的文件

            Sheet sheet = workbook.getSheetAt(0);

            // 获取任务表头，用于统一读取不同格式excel
            List<String> headers = new ArrayList<>();

            // 逐行读取
            Iterator<Row> rowIterator = sheet.iterator();
            boolean isHead = true;

            while (rowIterator.hasNext()) {

                Row row = rowIterator.next();
                if(isHead){
                    for (Cell cell:row){
                        headers.add(cell.getStringCellValue());
                    }
                    isHead = false;
                    continue;
                }
                if (isRowEmpty(row)) continue; // 跳过空行

                taskInfo record = new taskInfo();
                fillTaskInfo(record, row, headers);
                dataList.add(record);

            }

        } catch (IOException e) {
            handleException("读取任务信息表失败", e);
        }
        return dataList;
    }

    /**
     * 读取任务信息表（带空数据安全处理）
     */
    public static List<taskInfo> readTaskInfoActually(String filePath) {

        List<taskInfo> dataList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = StreamingReader.builder()
                     .rowCacheSize(10 * 10) //缓存到内存中的行数，默认是10
                     .bufferSize(1024 *4) //读取资源时，缓存到内存的字节大小，默认是1024
                     .open(fis)) { //打开资源，可以是InputStream或者是File，只能打开.xlsx格式的文件

            Sheet sheet = workbook.getSheetAt(0);

            // 获取任务表头，用于统一读取不同格式excel
            List<String> headers = new ArrayList<>();

            // 逐行读取
            Iterator<Row> rowIterator = sheet.iterator();
            boolean isHead = true;

            while (rowIterator.hasNext()) {

                Row row = rowIterator.next();
                if(isHead){
                    for (Cell cell:row){
                        headers.add(cell.getStringCellValue());
                    }
                    isHead = false;
                    continue;
                }
                if (isRowEmpty(row)) continue; // 跳过空行

                taskInfo record = new taskInfo();
                fillTaskInfoActual(record, row, headers);
                dataList.add(record);

            }

        } catch (IOException e) {
            handleException("读取任务信息表失败", e);
        }
        return dataList;
    }


    public static List<ActualData> readActualData(String filePath) {
        List<ActualData> dataList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = StreamingReader.builder()
                     .rowCacheSize(10 * 10)
                     .bufferSize(1024 * 4)
                     .open(fis)) {

            Sheet sheet = workbook.getSheetAt(0);

            // 获取表头，用于统一读取不同格式excel
            List<String> headers = new ArrayList<>();

            Iterator<Row> rowIterator = sheet.iterator();
            boolean isHead = true;

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();

                if (isHead) {
                    for (Cell cell : row) {
                        headers.add(cell.getStringCellValue());
                    }
                    isHead = false;
                    continue;
                }

                if (isRowEmpty(row)) continue; // 跳过空行

                ActualData record = new ActualData();
                fillActualData(record, row, headers);
                dataList.add(record);
            }

        } catch (IOException e) {
            handleException("读取实际生产数据表失败", e);
        }
        return dataList;
    }

    /**
     * 读取零件信息表（带空数据安全处理）
     */
    public static List<item> readPartRecord(String filePath) {
        List<item> dataList = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            skipHeader(rowIterator);

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (isRowEmpty(row)) continue;

                item record = new item();
                fillItemInfo(record, row);
                dataList.add(record);
            }
        } catch (IOException e) {
            handleException("读取零件信息表失败", e);
        }
        return dataList;
    }

    /**
     * 读取人员信息表（带空数据安全处理）
     */
    public static List<worker> readEmployeeRecord(String filePath) {
        List<worker> dataList = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            skipHeader(rowIterator);

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (isRowEmpty(row)) continue;

                worker record = new worker();
                //直接把制造单元为空，能力系数和效率系数不为合法数的数据过滤掉
                if(!getCellString(row, 3) .equals("-")&&
                        isValidDouble(getCellString(row,4))&&
                        isValidDouble(getCellString(row,5))
                ){
                    fillWorkerInfo(record, row);
                    dataList.add(record);
                }
            }
        } catch (IOException e) {
            handleException("读取人员信息表失败", e);
        }
        return dataList;
    }

    /**
     * 检测字符串是否为合法Double数
     */
    public static boolean isValidDouble(String str) {
        if (str == null || str.trim().isEmpty()) {
            return false;
        }
        try {
            Double.parseDouble(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }

    //------------------------ 安全填充方法 ------------------------
    private static void fillTaskInfo(taskInfo record, Row row,List<String> headers) {
        for (int i = 0; i < headers.size(); i++) {
            switch (headers.get(i)) {
                case "序号":
                    record.setId(getCellString(row, i));
                    break;
                case "工作订单号":
                    record.setWorkOrderNumber(getCellString(row, i));
                    break;
                case "型号系列":
                    record.setModelSeries(getCellString(row, i));
                    break;
                case "型号":
                    record.setModel(getCellString(row, i));
                    break;
                case "作业令号":
                    record.setOperationOrderNumber(getCellString(row, i));
                    break;
                case "批架次":
                    record.setBatchNumber(getCellString(row, i));
                    break;
                case "临时令单号":
                    record.setTemporaryOrderNumber(getCellString(row, i));
                    break;
                case "零件图号":
                    record.setItemID(getCellString(row, i));
                    break;
                case "零件名称":
                    record.setItemName(getCellString(row, i));
                    break;
                case "合令令单码":
                    record.setOrderCode(getCellString(row, i));
                    break;
                case "流卡令单码":
                    record.setFlowOrderCode(getCellString(row, i));
                    break;
                case "任务数":
                    record.setTaskCount(getCellInteger(row, i));
                    break;
                case "交付单位":
                    record.setDeliveryUnit(getCellString(row, i));
                    break;
                case "中止/下发状态":
                    record.setSuspendOrIssuedStatus(getCellString(row, i));
                    break;
                case "计划需求时间":
                    record.setDeadline(LocalDate.parse(getCellString(row, i)));
                    break;
                case "投产类别":
                    record.setProductionCategory(getCellString(row, i));
                    break;
                case "主路线":
                    record.setMainRoute(getCellString(row, i));
                    break;
                case "分工路线":
                    record.setSubRoute(getCellString(row, i));
                    break;
                case "所属部件":
                    record.setAssociatedComponent(getCellString(row, i));
                    break;
                case "密级":
                    record.setConfidentialityLevel(getCellString(row, i));
                    break;
                default:
                    // 未定义字段默认不处理
            }
        }
    }

    private static void fillActualData(ActualData record, Row row, List<String> headers) {
        for (int i = 0; i < headers.size(); i++) {
            switch (headers.get(i)) {
                case "序号":
                    record.setId(getCellString(row, i));
                    break;
                case "零件图号":
                    record.setItemID(getCellString(row, i));
                    break;
                case "零件名称":
                    record.setItemName(getCellString(row, i));
                    break;
                case "令单码":
                    record.setOrderCode(getCellString(row, i));
                    break;
                case "申请入库数量":
                    record.setInventoryNumber(getCellInteger(row, i));
                    break;
                case "交付单位":
                    record.setDeliveryUnit(getCellString(row, i));
                    break;
                case "完工移交单时间":
                    String dateStr = getCellString(row, i);
                    if (!"null".equals(dateStr)) {
                        record.setDeadline(LocalDate.parse(dateStr));
                    }
                    break;
                case "成品工段":
                    record.setWorkcenterName(getCellString(row, i));
                    break;
                case "加工周期":
                    record.setMakeCycle(getCellDouble(row, i, 0.0));
                    break;
                case "工时":
                    record.setTotalTime(getCellDouble(row, i, 0.0));
                    break;
                case "完成月份":
                    record.setMonth(getCellString(row, i));
                    break;
                default:
                    // 未定义字段默认不处理

            }
        }
    }
    private static void fillTaskInfoActual(taskInfo record, Row row, List<String> headers) {
        for (int i = 0; i < headers.size(); i++) {
            switch (headers.get(i)) {
                case "序号":
                    record.setId(getCellString(row, i));
                    break;
                case "零件图号":
                    record.setItemID(getCellString(row, i));
                    break;
                case "零件名称":
                    record.setItemName(getCellString(row, i));
                    break;
                case "令单码":
                    record.setOrderCode(getCellString(row, i));
                    break;
                case "申请入库数量":
                    record.setTaskCount(getCellInteger(row, i));
                    break;
                case "交付单位":
                    record.setDeliveryUnit(getCellString(row, i));
                    break;
                case "完工移交单时间":
                    String dateStr = getCellString(row, i);
                    if (!"null".equals(dateStr)) {
                        record.setDeadline(LocalDate.parse(dateStr));
                    }
                    break;
                default:
                    // 未定义字段默认不处理
                    record.setFlowOrderCode("null");
            }
        }
    }

    private static void fillItemInfo(item record, Row row) {
        record.setItemId(getCellString(row, 0));
        record.setItemName(getCellString(row, 1));
        record.setItemDifficulty(getCellDouble(row, 2, 0.0));
        record.setWorkcenterName(getCellString(row, 3));
        record.setPreCycle(getCellDouble(row,4,0.0));
        record.setMakeCycle(getCellDouble(row, 6, 0.0));
    }

    private static void fillWorkerInfo(worker record, Row row) {
        record.setId(getCellString(row, 0));
        record.setEmployeeName(getCellString(row, 1));
        record.setEmployeeNo(getCellString(row, 2));
        if(getCellString(row, 3) .equals("-"))
        {
            record.setMdsWorkerGroupName("null");
        }else{
            record.setMdsWorkerGroupName(getCellString(row, 3));
        }
        record.setCapacityCodfficient(getCellDouble(row, 4, 0.0)); // 默认效率系数1.0
        record.setEfficiencyCodfficient(getCellDouble(row, 5, 0.0));
    }

    //------------------------ 核心工具方法 ------------------------
    private static String getCellString(Row row, int column) {
        Cell cell = row.getCell(column, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        return cell != null ? parseCellValue(cell) : "null";
    }

    private static Integer getCellInteger(Row row, int column) {
        Cell cell = row.getCell(column, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        return parseNumericValue(cell, Double::intValue);
    }

    private static Double getCellDouble(Row row, int column, Double defaultValue) {
        Cell cell = row.getCell(column, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        Double value = parseNumericValue(cell, d -> d);
        return value != null ? value : defaultValue;
    }

    //------------------------ 底层解析方法 ------------------------
    private static String parseCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                return DateUtil.isCellDateFormatted(cell)
                        ? cell.getDateCellValue().toString()
                        : String.valueOf((long) cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "null";
        }
    }

    private static <T> T parseNumericValue(Cell cell, java.util.function.Function<Double, T> converter) {
        if (cell == null) return null;
        try {
            return converter.apply(cell.getNumericCellValue());
        } catch (IllegalStateException e) {
            // 处理字符串类型的数字
            String strVal = cell.getStringCellValue().trim();
            if (!strVal.isEmpty()) {
                try {
                    return converter.apply(Double.parseDouble(strVal));
                } catch (NumberFormatException ex) {
                    return null;
                }
            }
        }
        return null;
    }

    //------------------------ 辅助方法 ------------------------
    private static void skipHeader(Iterator<Row> rowIterator) {
        if (rowIterator.hasNext()) rowIterator.next();
    }

    private static boolean isRowEmpty(Row row) {
        if (row == null) return true;
        for (Cell cell : row) {
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    private static void handleException(String message, Exception e) {
        System.err.println(message + ": " + e.getMessage());
        e.printStackTrace();
    }
}